CREATE OR REPLACE FUNCTION evaluate_Referred_and_Seriousness_ticket_features1() RETURNS VOID AS '
DECLARE
 
  v_countLeadTicketsMatched INTEGER;
  v_countLeadTicketsFound INTEGER;
  v_countLeadTicketsTheir INTEGER;
  v_leadRecall REAL;
  v_leadPrecision REAL;


v_bigSMHTicketsMatched INTEGER;
  v_bigSMHTicketsFound INTEGER;
  v_bigSMHTicketsTheir INTEGER;
  v_bigSMHRecall REAL;
  v_bigSMHPrecision REAL;

  v_notELINTicketsMatched INTEGER;
  v_notELINTicketsFound INTEGER;
  v_notELINTicketsTheir INTEGER;
  v_notELINRecall REAL;
  v_notELINPrecision REAL;


BEGIN

--Query

--ReferredMetadata 

select count(*) INTO v_countLeadTicketsFound
from referredtickets t
where   t.leadticket is not null and
t.referredticket is not null and leadticket!=referredticket;

 select count(*) INTO v_countLeadTicketsMatched
 from referredtickets t, new_referredtickets_1996_2006 e
 where   t.leadticket is not null and  
 (t.leadticket = e.referredto and t.referredticket = e.ecsticket)
 and (t.leadticket!=t.referredticket) and e.ecsticket!=e.referredto;

select count(*) INTO v_countLeadTicketsTheir
from new_referredtickets_1996_2006 where referredto!=ecsticket;

--BigSMHTickets

select count(*) INTO v_bigSMHTicketsMatched 
from ticketfeatures t, "big_serious_smh_ticket_fire_blow_smok" s  
where t.troubletype  in (''SMH'') and seriousmetadata is not null
 and s.ticket = t.ticketnumber and t.seriousmetadata=1 and s.ser1_notser0=1;

 select count(*) INTO v_bigSMHTicketsFound
from ticketfeatures t, "big_serious_smh_ticket_fire_blow_smok" s  
where t.troubletype  in (''SMH'') and seriousmetadata is not null
 and s.ticket = t.ticketnumber and t.seriousmetadata=1;

  select count(*) INTO v_bigSMHTicketsTheir
from ticketfeatures t, "big_serious_smh_ticket_fire_blow_smok" s  
where t.troubletype  in (''SMH'') and seriousmetadata is not null
 and s.ticket = t.ticketnumber and  s.ser1_notser0=1;

--NOT ELIN


select count(*) INTO v_notELINTicketsMatched
from ticketfeatures t, "serious_tkt_notMHevent_notELIN_fire_blow_smok" s 
 where t.troubletype not in (''SMH'', ''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null 
 and s.ticket = t.ticketnumber  and t.seriousmetadata =1 and s.ser1_notser0=1;

 select count(*) INTO v_notELINTicketsTheir
from ticketfeatures t, "serious_tkt_notMHevent_notELIN_fire_blow_smok" s 
 where t.troubletype not in (''SMH'', ''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null 
 and s.ticket = t.ticketnumber  and s.ser1_notser0 =1;

 select count(*) INTO  v_notELINTicketsFound
from ticketfeatures t, "serious_tkt_notMHevent_notELIN_fire_blow_smok" s 
 where t.troubletype not in (''SMH'', ''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null 
 and s.ticket = t.ticketnumber  and t.seriousmetadata =1;




--Calculation


--BigSMHTickets
v_bigSMHRecall := CAST(v_bigSMHTicketsMatched as REAL) / CAST(v_bigSMHTicketsTheir as REAL);
v_bigSMHPrecision := CAST(v_bigSMHTicketsMatched as REAL) / CAST(v_bigSMHTicketsFound as REAL);
  
 --ReferredMetadata

v_leadRecall := CAST(v_countLeadTicketsMatched as REAL) / CAST(v_countLeadTicketsTheir as REAL);
v_leadPrecision := CAST(v_countLeadTicketsMatched as REAL) / CAST(v_countLeadTicketsFound as REAL);


-- NOT ELIN
  v_notELINRecall := CAST(v_notELINTicketsMatched as REAL) / CAST(v_notELINTicketsTheir as REAL);
v_notELINPrecision := CAST(v_notELINTicketsMatched as REAL) / CAST(v_notELINTicketsFound as REAL);
  


  --Print


  RAISE NOTICE ''=============================================='';
  RAISE NOTICE ''Big SMH Tickets: recall: % precision % '',  v_bigSMHRecall, v_bigSMHPrecision;
  RAISE NOTICE ''Not ELIN Tickets: recall: % precision % '',  v_notELINRecall, v_notELINPrecision;
  RAISE NOTICE ''=============================================='';
  RAISE NOTICE ''ReferredMetadata: recall: % precision % '',  v_leadRecall, v_leadPrecision;
  RAISE NOTICE ''=============================================='';
END;
' LANGUAGE 'plpgsql';

-- SELECT evaluate_Referred_and_Seriousness_ticket_features1();